import xlrd
import sqlite3
import matplotlib.pyplot as plt
import numpy as np

def line():
	#  workbook will be the .xls file we want to explore
	workbook = xlrd.open_workbook('IPC_Portugal_1977_2013.xls')

	#  worksheet will be the data sheet we will be working with
	worksheet = workbook.sheet_by_index(0)


	#  The first list sorts the data by year
	data_by_year = [[worksheet.cell_value(nrow, ncol) for ncol in range(worksheet.ncols)] for nrow in range(worksheet.nrows)]

	#  This for loop will circle through the nested list and eliminate the firts column, which is only blank and unused space
	for i in range(worksheet.nrows):
		if data_by_year[i][0] == '':
			del(data_by_year[i][0])
		


	#  This connection will generate the database.db file that we want to place our data on
	connection = sqlite3.connect('database.db')

	#  This will be the controller of our database
	c = connection.cursor()

	#  Creation of a function that will generate and empty table called Data, with the 9 parameters of the excel sheet.
	def create_table():
		c.execute('CREATE TABLE IF NOT EXISTS Data (Ano integer, IPCGlobal real, Variacao_Anual real, Renumeracao_Minima_Mensal real, Renumeracao_Maxima_Mensal real, PIB_per_capita_anual real, Rendimento_nacional_bruto_per_capita_anual real, Rendimento_disponivel_bruto_per_capita_anual real, Remuneracoes_per_capita_anual real)')

	#  Calling creating_table function	
	create_table()


	#  Creation of a function that will place the values in the correct parameters. The function will skip the first row of the excel sheet, since it has text and not the values that we are interested
	def data_entry():
		for i in range(worksheet.nrows):
			if i == 0:  #  If it's the first row, then...
				pass    #  ... ignore it... 
			else:
				c.execute("INSERT INTO Data VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)", data_by_year[i])  #  ... and if it's not, place the values. Each element of the row will be place in it's correspondent '?'	
		
		connection.commit()  #  This will save the database file

	#  Calling data_entry function 
	data_entry()	




	# The following three lines will recieve the inputs for the typo of data, starting year and ending year for the graph
	type_of_data = raw_input("Escolha que estatistica pretente visualizar em grafico: ")
	min_year = int(raw_input("A partir de que ano pretende ver a respetiva estatistica? "))
	max_year = int(raw_input("Ate que ano pretende ver a respetiva estatistica? "))

	graph_array = []
	year_array = []

	# The for cycle will year by year select the corresponding data from the column chosen to be seen on the graph, and will append each year data to an array
	for i in range(min_year, max_year + 1):
		year_array.append(i)
		for row in c.execute('SELECT ' + type_of_data + ' FROM Data WHERE Ano = ? ', [(str(i))]):
			starting_info = str(row).replace(')','').replace('(','').replace('u\'','').replace("'","").replace(',', '')
			data_to_use = float(starting_info)
			graph_array.append(data_to_use)

	
	fig = plt.figure()
	rect = fig.patch
	rect.set_facecolor('#31312e')

	# This will create the line plot desired
	ax1 = fig.add_subplot(1,1,1, axisbg='grey')
	ax1.plot(year_array, graph_array, 'c', linewidth=3.3)

	# This will set the tick colors to cyan
	ax1.tick_params(axis='x', colors='c')
	ax1.tick_params(axis='y', colors='c')

	# And this will put the borders of the graph to white
	ax1.spines['bottom'].set_color('white')
	ax1.spines['top'].set_color('white')
	ax1.spines['right'].set_color('white')
	ax1.spines['left'].set_color('white')

	# This will set the text to the color cyan
	ax1.yaxis.label.set_color('c')
	ax1.xaxis.label.set_color('c')

	# And this will define the title and text labels
	ax1.set_title(type_of_data, color='c')
	ax1.set_xlabel('Ano')
	ax1.set_ylabel(type_of_data)

	# Finally, it shows the line graph
	plt.show()
	
# If it's Bar Chart (1)...	
def bar():	

	#  workbook will be the .xls file we want to explore
	workbook = xlrd.open_workbook('IPC_Portugal_1977_2013.xls')

	#  worksheet will be the data sheet we will be working with
	worksheet = workbook.sheet_by_index(0)


	#  The first list sorts the data by year
	data_by_year = [[worksheet.cell_value(nrow, ncol) for ncol in range(worksheet.ncols)] for nrow in range(worksheet.nrows)]

	#  This for loop will circle through the nested list and eliminate the firts column, which is only blank and unused space
	for i in range(worksheet.nrows):
		if data_by_year[i][0] == '':
			del(data_by_year[i][0])
		


	#  This connection will generate the database.db file that we want to place our data on
	connection = sqlite3.connect('database.db')

	#  This will be the controller of our database
	c = connection.cursor()

	#  Creation of a function that will generate and empty table called Data, with the 9 parameters of the excel sheet.
	def create_table():
		c.execute('CREATE TABLE IF NOT EXISTS Data (Ano integer, IPCGlobal real, Variacao_Anual real, Renumeracao_Minima_Mensal real, Renumeracao_Maxima_Mensal real, PIB_per_capita_anual real, Rendimento_nacional_bruto_per_capita_anual real, Rendimento_disponivel_bruto_per_capita_anual real, Remuneracoes_per_capita_anual real)')

	#  Calling creating_table function	
	create_table()


	#  Creation of a function that will place the values in the correct parameters. The function will skip the first row of the excel sheet, since it has text and not the values that we are interested
	def data_entry():
		for i in range(worksheet.nrows):
			if i == 0:  #  If it's the first row, then...
				pass    #  ... ignore it... 
			else:
				c.execute("INSERT INTO Data VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)", data_by_year[i])  #  ... and if it's not, place the values. Each element of the row will be place in it's correspondent '?'	
		
		connection.commit()  #  This will save the database file

	#  Calling data_entry function 
	data_entry()	




	# The following three lines will recieve the inputs for the typo of data, starting year and ending year for the graph
	type_of_data = raw_input("Escolha que estatistica pretente visualizar em grafico: ")
	min_year = int(raw_input("A partir de que ano pretende ver a respetiva estatistica? "))
	max_year = int(raw_input("Ate que ano pretende ver a respetiva estatistica? "))

	graph_array = []
	year_array = []

	# The for cycle will year by year select the corresponding data from the column chosen to be seen on the graph, and will append each year data to an array
	for i in range(min_year, max_year + 1):
		year_array.append(i)
		for row in c.execute('SELECT ' + type_of_data + ' FROM Data WHERE Ano = ? ', [(str(i))]):
			starting_info = str(row).replace(')','').replace('(','').replace('u\'','').replace("'","").replace(',', '')
			data_to_use = float(starting_info)
			graph_array.append(data_to_use)
	
	# This will create the subplots and the width of the bars, respectively
	fig, ax = plt.subplots()
	width = 0.8
	
	# These next code lines calculate the best position for the x ticks to by placed (middle of the bar)
	tick_locations = np.arange(min(year_array), (max(year_array) + 1))
	rect_locations = []
	for i in tick_locations:
		rect_locations.append(i - (width/2.0))
	
	# This will set up the bars with the information passed	
	ax.bar(rect_locations, graph_array, width, color='wheat', edgecolor='#8B7E66', linewidth=4.0)
	
	# And this will define the title and text labels
	ax.set_title(type_of_data, color='c')
	ax.set_xlabel('Ano')
	ax.set_ylabel(type_of_data)
	
	# Finally, it shows the line graph
	plt.show()


	



